package com.apes.fn.ssm.repository;

import com.alibaba.fastjson.JSONArray;
import com.apes.fn.ssm.model.StockCalcuResult;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface StockCalcuResultRepository extends CustomRepository<StockCalcuResult, String> {
    @Query(value = "SELECT * FROM STOCK_CALCU_RESULT A WHERE ROWNUM > :minPage AND ROWNUM <= :maxPage ", nativeQuery = true)
    List<Map> downCalcuItem(@Param("maxPage") int maxPage, @Param("minPage") int minPage);


    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT A.LOCATION_ID, " +
            "                (SELECT NAME FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) LOCATION_NAME, " +
            "                A.FITTING_MESSAGE_ID, " +
            "                (SELECT NAME " +
            "                    FROM MAS_FITTING_MESSAGE " +
            "                   WHERE ID = A.FITTING_MESSAGE_ID) FITTING_MESSAGE_NAME, " +
            "                COUNT(PRODUCT_ID) STOCK_SKU_QTY, " +
            "                SUM(SAFETY_STOCK_QTY) SAFETY_STOCK_QTY, " +
            "                MAX(DIS_ALL_SUM_VIP / ALL_SUM_VIP) VIP_COVERGE, " +
            "                MAX(SKU_COVER) SKU_COVER, " +
            "                (SELECT COUNT(1) " +
            "                    FROM STOCK_CALCU_RESULT J " +
            "                   WHERE J.LOCATION_ID = A.LOCATION_ID " +
            "                     AND J.FITTING_MESSAGE_ID = A.FITTING_MESSAGE_ID) SALES_SKU_COUNT, " +
            "                (SELECT SUM(ACTUAL_SALES) " +
            "                    FROM STOCK_CALCU_RESULT J " +
            "                   WHERE J.LOCATION_ID = A.LOCATION_ID " +
            "                     AND J.FITTING_MESSAGE_ID = A.FITTING_MESSAGE_ID) ACTUAL_SALES " +
            "           FROM STOCK_CALCU_RESULT A, MAS_STOCK B " +
            "          WHERE A.LOCATION_ID = B.LOCATION_ID " +
            "            AND A.FITTING_MESSAGE_ID = B.FITTING_MESSAGE_ID " +
            "            AND STOCK_MARK = 1 " +
            "          GROUP BY A.LOCATION_ID, A.FITTING_MESSAGE_ID) " +
            " WHERE ROWNUM > :minPage " +
            "   AND ROWNUM <= :maxPage ", nativeQuery = true)
    List<Map> downCategoryAnalyze(@Param("maxPage") int maxPage, @Param("minPage") int minPage);

    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT A.LOCATION_ID, A.FITTING_MESSAGE_ID, " +
            "                COUNT(A.PRODUCT_ID) STOCK_PRO_COUNT, " +
            "                SUM(SAFETY_STOCK_QTY) SAFETY_STOCK_QTY," +
            "                (SELECT NAME FROM MAS_BRAND J WHERE J.ID = B.BRAND_ID) BRAND_NAME " +
            "           FROM STOCK_CALCU_RESULT A, MAS_CALCULATE_PRODUCT B " +
            "          WHERE A.LOCATION_ID = B.LOCATION_ID " +
            "            AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "            AND STOCK_MARK = 1 " +
            "          GROUP BY A.LOCATION_ID, A.FITTING_MESSAGE_ID, BRAND_ID) " +
            " WHERE ROWNUM > 0 " +
            "   AND ROWNUM <= 2000", nativeQuery = true)
    List<Map> downBrandAnalyze(@Param("maxPage") int maxPage, @Param("minPage") int minPage);
}
